import pool from '../config/db';
import { attributes } from '../types/attributes';
import { ResultSetHeader } from 'mysql2/promise';

// 创建标签的服务层函数
export const addRibute = async (user_id: number, family_id: string, attributes_name: string): Promise<string> => {
    try {
        // 先查询是否已存在相同的标签
        const checkQuery = 'SELECT * FROM attributes WHERE family_id = ? AND attributes_name = ?';
        const [rows] = await pool.query(checkQuery, [family_id, attributes_name]);
        // 判断查询结果是否为数组并且是否有记录
        if (Array.isArray(rows) && rows.length > 0) {
            return '该标签已存在'; // 返回提示信息
        }
        // 如果标签不存在，则插入新的标签记录
        const insertQuery = 'INSERT INTO attributes (attributes_name, user_id, family_id, ascription) VALUES (?, ?, ?, ?)';
        await pool.query(insertQuery, [attributes_name, user_id, family_id, 'private']); // ascription 设置为 'private'
        return '标签创建成功'; // 返回成功信息
    }
    catch (error) {
        console.error('Error creating tag:', error);
        throw new Error('创建标签时发生错误'); // 捕获错误并抛出
    }
};

// 获取公共标签和家庭自定义标签
export const getTagsByUserAndFamily = async (user_id: number, family_id: string) => {
    try {
        // 查询公共标签和家庭自定义标签（合并两者）
        const query = `
            SELECT id, attributes_name, ascription 
            FROM attributes 
            WHERE ascription IN ('public', 'private') AND (ascription = 'public' OR family_id = ?)
        `;
        const [tags] = await pool.query(query, [family_id]);
        // 返回合并后的标签列表
        return tags;
    } catch (error) {
        console.error('Error fetching tags:', error);
        throw new Error('无法获取标签数据');
    }
};

// 删除自定义标签 
export const deleteRibute = async (attributes_id: number, user_id: number, family_id: string) => {
    try {
        // 1. 查询items表，查看是否有物品使用该分类
        const checkQuery = ` SELECT * FROM items WHERE family_id = ? AND attributes_id = ? AND status NOT IN (1, 4, 5); `;
        const [items] = await pool.query(checkQuery, [family_id, attributes_id]);

        // 2. 如果该分类下有物品，status 不为 1, 4, 5，无法删除该标签
        if (Array.isArray(items) && items.length > 0) {
            return '该分类下还有物品，无法删除'; // 返回提示信息
        }
        // 3. 如果没有物品关联该分类，或所有物品 status 为 4，修改标签的 status 为 1
        const updateQuery = `
        UPDATE attributes 
        SET status = 1 
        WHERE id = ? AND family_id = ?
      `;
        const [result] = await pool.query(updateQuery, [attributes_id, family_id]);
        // 4. 判断更新操作是否成功
        if ((result as any).affectedRows > 0) {
            return '标签已删除'; // 返回成功信息
        } else {
            return '标签删除失败，未找到该分类';
        }
    } catch (error) {
        console.error('Error deleting tag:', error);
        throw new Error('删除标签时发生错误');
    }
};